CREATE TRIGGER
CREATE TRIGGER — Define a new trigger
Synopsis
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event
[ OR ... ] }
ON table_name
[ FROM referenced_table_name ]
[ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
[ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name }
[ ... ] ]
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )
where event can be one of:
INSERT
UPDATE [ OF column_name [, ... ] ]
DELETE
TRUNCATE
Description
CREATE TRIGGER creates a new trigger. The trigger will be associated with the specified table, view, or foreign table and will execute the specified function function_name when certain operations are performed on the table.
The trigger can be specified to fire before the operation is attempted on a row (before constraints are checked and the INSERT, UPDATE, or DELETE is attempted); after the operation has completed (after constraints are checked and the INSERT, UPDATE, or DELETE has completed); or instead of the operation (in the case of inserts, updates, or deletes on a view). If the trigger fires before or instead of the event, the trigger can skip the operation for the current row or change the row being inserted (only for INSERT and UPDATE operations). If the trigger fires after the event, all changes (including the effects of other triggers) are "visible" to the trigger.
A trigger marked as FOR EACH ROW is called once for each row modified by the operation. For example, a DELETE that affects 10 rows will cause any ON DELETE triggers on the target relation to be called 10 times independently, once for each deleted row. In contrast, a trigger marked as FOR EACH STATEMENT only executes once for any given operation, regardless of how many rows the operation modifies (in particular, an operation that modifies zero rows will still cause any applicable FOR EACH STATEMENT triggers to be executed).
A trigger specified to fire INSTEAD OF the trigger event must be marked as FOR EACH ROW and can only be defined on views. BEFORE and AFTER triggers on a view must be marked as FOR EACH STATEMENT.
Additionally, triggers can be defined to fire on TRUNCATE, but only as FOR EACH STATEMENT.
A trigger definition can specify a boolean WHEN condition, which will be tested to determine whether the trigger should fire. In row-level triggers, the WHEN condition can check the new and old values of the row's columns. Statement-level triggers can also have WHEN conditions, although this feature is not very useful for them (since the condition cannot reference any values in the table).
If multiple triggers of the same type are defined to fire for the same event, they will fire in alphabetical order by name. When the CONSTRAINT option is specified, this command creates a constraint trigger. This is the same as a regular trigger, except that the timing of the trigger can be adjusted using SET CONSTRAINTS. Constraint triggers must be AFTER ROW triggers on tables. They can be fired at the end of the statement causing the triggering event, or at the end of the transaction containing the statement. In the latter case, they are said to be deferred. Pending deferred triggers can also be forced to fire immediately using SET CONSTRAINTS. When the constraint implemented by a constraint trigger is violated, the constraint trigger should raise an exception.
The REFERENCING option enables the collection of transition relations, which are row sets containing the rows inserted, deleted, or modified by the current SQL statement. This feature allows a trigger to see the global view of what the statement did, rather than seeing one row at a time. This option is only allowed for AFTER triggers that are not constraint triggers. Furthermore, if the trigger is an UPDATE trigger, it cannot specify a column_name list. OLD TABLE can only be specified once, and only for triggers fired on UPDATE or DELETE events; it creates a transition relation containing the before-images of all rows updated or deleted by the statement. Similarly, NEW TABLE can only be specified once, and only for triggers fired on UPDATE or INSERT events; it creates a transition relation containing the after-images of all rows updated or inserted by the statement.
SELECT does not modify any rows, so you cannot create SELECT triggers. Rules and views can provide viable solutions for problems that require SELECT triggers.
Parameters
name
The name to give the new trigger. This must be distinct from the name of any other trigger on the same table. The name cannot be schema-qualified — the trigger inherits the schema of its table. For a constraint trigger, this is also the name used when modifying the trigger's behavior with SET CONSTRAINTS.
BEFORE
AFTER
INSTEAD OF
Determines whether the function is called before, after, or instead of the event. A constraint trigger can also be specified as AFTER.
event
One of INSERT, UPDATE, DELETE, or TRUNCATE; this specifies the event that will fire the trigger. Multiple events can be specified with OR, except when transition relations are requested.
For UPDATE events, a column list can be specified using the following syntax:
UPDATE OF column_name1 [, column_name2 ... ]
The trigger will only fire if at least one of the listed columns appears as a target of the UPDATE command's SET list, or if one of the listed columns is a generated column and the column it depends on is a target of the UPDATE.
INSTEAD OF UPDATE events do not allow a column list. A column list cannot be specified when requesting transition relations either.
table_name
The name (optionally schema-qualified) of the table, view, or foreign table for which the trigger is to be used.
referenced_table_name
The name (optionally schema-qualified) of another table referenced by the constraint. This option is used for foreign key constraints and is not recommended for general purposes. This can only be specified for constraint triggers.
DEFERRABLE
NOT DEFERRABLE
INITIALLY IMMEDIATE
INITIALLY DEFERRED
The default timing of the trigger. See the CREATE TABLE documentation for details on these constraint options. This can only be specified for constraint triggers.
REFERENCING
This keyword immediately precedes the declaration of one or two relation names that provide access to the transition relations of the triggering statement.
OLD TABLE
NEW TABLE
This clause indicates whether the following relation name is for the before-image transition relation or the after-image transition relation.
transition_relation_name
The (unqualified) name to use for this transition relation within the trigger.
FOR EACH ROW
FOR EACH STATEMENT
This specifies whether the trigger function should be fired once for each row affected by the trigger event, or once for each SQL statement. If neither is specified, FOR EACH STATEMENT is the default. Constraint triggers can only be specified as FOR EACH ROW.
condition
A Boolean expression that determines whether the trigger function will actually be executed. If WHEN is specified, the function will only be called when condition returns true. In FOR EACH ROW triggers, the WHEN condition can reference the old and new row values of columns by writing OLD.column_name or NEW.column_name respectively. Of course, INSERT triggers cannot reference OLD and DELETE triggers cannot reference NEW.
INSTEAD OF triggers do not support WHEN conditions.
Currently, WHEN expressions cannot contain subqueries.
Note that for constraint triggers, evaluation of the WHEN condition is not deferred, but occurs immediately after the row update operation is performed. If the condition does not evaluate to true, the trigger is not placed in the queue for deferred execution.
function_name
A user-supplied function that is declared to take no arguments and return type trigger, which will be executed when the trigger fires.
In the syntax of CREATE TRIGGER, the keywords FUNCTION and PROCEDURE are equivalent, but the referenced function must be a function, not a procedure, in any case. The use of the keyword PROCEDURE here is historical and has been deprecated.
arguments
An optional comma-separated list of arguments to be provided to the function when the trigger is executed. The arguments are string constants. Simple names and numeric constants can also be written here, but they will all be converted to strings. Check the description of the trigger function's implementation language to find out how to access these arguments within the function; this may differ from ordinary function arguments.
Notes
To create a trigger on a table, the user must have the TRIGGER privilege on the table. The user must also have the EXECUTE privilege on the trigger function.
Use DROP TRIGGER to remove a trigger.
A column-specific trigger (one defined using the UPDATE OF column_name syntax) will fire when its column is listed as a target of the UPDATE command's SET list. Even if the trigger is not fired, a column's value might change, because changes made by BEFORE UPDATE triggers to the row contents are not considered. Conversely, a command like UPDATE ... SET x = x ... will fire a trigger on column x, even though the column's value did not change.
There are built-in trigger functions available to solve common problems without the need to write your own trigger code.
In a BEFORE trigger, the WHEN condition is evaluated exactly when the function is or would be executed, so using WHEN is not substantially different from testing the same condition at the beginning of the trigger function. In particular, note that the condition sees the NEW row as it currently exists, though it may have already been modified by earlier triggers. Also, a BEFORE trigger's WHEN condition is not allowed to check system columns of the NEW row (such as ctid), because those columns have not been set yet.
In an AFTER trigger, the WHEN condition is evaluated immediately after the row update occurs, and it determines whether an event should be placed in the queue to fire the trigger at the end of the statement. Therefore, when an AFTER trigger's WHEN condition does not return true, there is no need to queue an event or re-fetch the row at the end of the statement. If the trigger only needs to be fired for some rows, this can significantly speed up statements that modify many rows.
In some cases, a single SQL command might fire multiple types of triggers. For example, an INSERT with an ON CONFLICT DO UPDATE clause may result in both insert and update operations, and it will fire both types of triggers as appropriate. The transition relations provided to the triggers are related to their event type, so INSERT triggers will only see inserted rows, and UPDATE triggers will only see updated rows.
Row updates or deletions caused by foreign key enforcement actions (such as ON UPDATE CASCADE or ON DELETE SET NULL) are treated as part of the SQL command that caused them. Relevant triggers on affected tables will be fired, providing another way for an SQL command to fire triggers that do not directly match its type. In simple cases, triggers requesting transition relations will see all changes made by the original SQL command in their tables in a single transition relation. However, in some cases, the presence of an AFTER ROW trigger requesting transition relations will cause foreign key enforcement actions triggered by a single SQL command to be split into multiple steps, each with its own transition relation. In this case, all existing statement-level triggers will be fired for each set of transition relations created, ensuring that those triggers can see each affected row in a transition relation once and only once.
Statement-level triggers on a view will only be fired if the action on the view is handled by a row-level INSTEAD OF trigger.
If the action is handled by an INSTEAD rule, then any statements issued by the rule are executed in place of the original statement referencing the view, so the triggers that will be fired are those on tables mentioned in the replacement statements. Similarly, if the view is auto-updatable, the action is handled by automatically rewriting the statement into an action on the view's base tables, so the statement-level triggers on the base tables are the ones that will be fired.
Creating a row-level trigger on a partitioned table will cause the same trigger to be created on all its existing partitions, and any partitions created or attached later will also contain an identical trigger. If a partition is detached from its parent, the trigger will be removed. Triggers on partitioned tables can only be AFTER.
Modifying a partitioned table or a table with inheritance children will fire statement-level triggers attached to the explicitly mentioned table, but will not fire statement-level triggers on its partitions or child tables. In contrast, row-level triggers will be fired on the affected partitions or child tables, even if they are not explicitly mentioned in the query. If a statement-level trigger is defined with a REFERENCING clause for transition relations, the before and after images of rows from all affected partitions or child tables are visible. In the case of inherited child tables, row images only include columns present in the table to which the trigger is attached. Currently, row-level triggers with transition relations cannot be defined on partitions or inherited child tables.
Examples
# Execute function check_account_update whenever a row in table accounts is about to be updated:
CREATE TRIGGER check_update
BEFORE UPDATE ON accounts
FOR EACH ROW
EXECUTE FUNCTION check_account_update();
# The following example is the same as the one above, but only executes the function when the UPDATE command specifies that the balance column is to be updated:
CREATE TRIGGER check_update
BEFORE UPDATE OF balance ON accounts
FOR EACH ROW
EXECUTE FUNCTION check_account_update();
# This form only executes the function when the balance column has actually been changed:
CREATE TRIGGER check_update
BEFORE UPDATE ON accounts
FOR EACH ROW
WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
EXECUTE FUNCTION check_account_update();
# Call a function to log updates to accounts, but only when something has changed:
CREATE TRIGGER log_update
AFTER UPDATE ON accounts
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE FUNCTION log_account_update();
# Execute function view_insert_row for each row to be inserted into the view's underlying table:
CREATE TRIGGER view_insert
INSTEAD OF INSERT ON my_view
FOR EACH ROW
EXECUTE FUNCTION view_insert_row();
# Execute function check_transfer_balances_to_zero for each statement to confirm that transfer rows do not have a net value increase:
CREATE TRIGGER transfer_insert
AFTER INSERT ON transfer
REFERENCING NEW TABLE AS inserted
FOR EACH STATEMENT
EXECUTE FUNCTION check_transfer_balances_to_zero();
# Execute function check_matching_pairs for each row to confirm that matching pairs are changed (by the same statement) at the same time:
CREATE TRIGGER paired_items_update
AFTER UPDATE ON paired_items
REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab
FOR EACH ROW
EXECUTE FUNCTION check_matching_pairs();
See Also
ALTER TRIGGER,DROP TRIGGER, CREATE FUNCTION, SET CONSTRAINTS